﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SQLite;
using System.IO;
using CSV;
using Microsoft.VisualBasic;
using System.Runtime.InteropServices;
using System.Globalization;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        const double workingday = 9.5;
        protected string FileName;
        protected bool Modified;
        private const int MaxColumns = 3;
        string log = "";
        int count = 0;
        bool warnimg = false;
        string pwarnmsg = "";
        string firstupdate = "";
        string lastupdate = "";
        int rowcount = 0;

        public Form1()
        {
            InitializeComponent();
            InitializeGrid();
            InitializeUpdate();
            string query = "SELECT workerid FROM users WHERE working = 1 ORDER BY workerid ASC";
            FillDropDownList(query, cbxENumber);
            FillDropDownList(query, comboBox1);
            string querya = "SELECT workerid FROM users ORDER By workerid ASC";
            FillDropDownList(querya, cbxEAdmin);
            string query2 = "SELECT workerid FROM Users where working = 0 ORDER By workerid ASC";
            FillDropDownList(query2, cbxRein);
            FillDropDownList(querya, cbxReport);
            cbxType.SelectedIndex = 0;
            ReadData(ref firstupdate, ref lastupdate);
            setMaxmin();
        }

        private void ReadData(ref string FirstUpdate, ref string LastUpdate)
        {
            StreamReader read = new StreamReader("data.dat");
            FirstUpdate = read.ReadLine();
            LastUpdate = read.ReadLine();
            read.Close();
        }

        private void WriteData(ref string FirstUpdate, ref string LastUpdate)
        {
            StreamWriter write = new StreamWriter("data.dat");
            write.WriteLine(FirstUpdate);
            write.WriteLine(LastUpdate);
            write.Close();
        }

        private void picwarn_MouseDown(object sender, MouseEventArgs e)
        {
            if ((e.Button & MouseButtons.Left) == MouseButtons.Left)
            {
                var imagePos = e.Location; // that's it
                timerpic.Enabled = false;
                picwarn.Visible = true;
                MessageBox.Show(pwarnmsg);
                picwarn.Visible = false;

            }
        }
        public static void FillDropDownList(string Query, ComboBox DropDownName)
        {
            SQLiteDataReader dr;
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();

            //Check whether the Drop Down has existing items. If YES, empty it.
            if (DropDownName.Items.Count > 0)
                DropDownName.Items.Clear();
            SQLiteCommand cmd = new SQLiteCommand(Query, con);
            dr = cmd.ExecuteReader();
            while (dr.Read())
                DropDownName.Items.Add(dr[0].ToString());
            dr.Close();
            con.Close();
        }
        private void InitializeUpdate()
        {
            lblMon.Visible = false;
            lblTue.Visible = false;
            lblWed.Visible = false;
            lblThur.Visible = false;
            lblFri.Visible = false;
            lblSat.Visible = false;
            lblSun.Visible = false;
            cbxMon.Visible = false;
            cbxTue.Visible = false;
            cbxWed.Visible = false;
            cbxThur.Visible = false;
            cbxFri.Visible = false;
            cbxSat.Visible = false;
            cbxSun.Visible = false;
            btnUpdateE.Visible = false;
            cbxMon.SelectedIndex = 0;
            cbxTue.SelectedIndex = 0;
            cbxWed.SelectedIndex = 0;
            cbxThur.SelectedIndex = 0;
            cbxFri.SelectedIndex = 0;
            cbxSat.SelectedIndex = 0;
            cbxSun.SelectedIndex = 0;
        }

        private void ButtonOnclickUpdate()
        {
            lblMon.Visible = true;
            lblTue.Visible = true;
            lblWed.Visible = true;
            lblThur.Visible = true;
            lblFri.Visible = true;
            lblSat.Visible = true;
            lblSun.Visible = true;
            cbxMon.Visible = true;
            cbxTue.Visible = true;
            cbxWed.Visible = true;
            cbxThur.Visible = true;
            cbxFri.Visible = true;
            cbxSat.Visible = true;
            cbxSun.Visible = true;
            btnUpdateE.Visible = true;
        }

        private void exitToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void btnTest_Click(object sender, EventArgs e)
        {
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            string sql = "SELECT * From users ORDER By workerid ASC";
            SQLiteCommand command = new SQLiteCommand(sql, con);
            SQLiteDataAdapter da = new SQLiteDataAdapter(command);
            DataSet ds = new DataSet();
            da.Fill(ds);
            DataTable dt = ds.Tables[0];
            this.grid.DataSource = dt;
            con.Close();
        }

        private void btnReset_Click(object sender, EventArgs e)
        {
            edtWCode.Text = "";
            edtWName.Text = "";
            edtWSurname.Text = "";
            cbxCWork.Text = "Yes";
            cbxStatus.Text = "Staff";
        }

        private void btnAddUer_Click(object sender, EventArgs e)
        {
            try
            {
                SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
                con.Open();
                int wwork = 0;
                int wstatus = 0;
                string wcode = edtWCode.Text;
                string wname = edtWName.Text;
                string wsname = edtWSurname.Text;
                if (this.cbxCWork.Items[this.cbxCWork.SelectedIndex].ToString() == "Yes")
                    wwork = 1;
                if (this.cbxStatus.Items[this.cbxStatus.SelectedIndex].ToString() == "Staff")
                    wstatus = 1;
                SQLiteCommand cmd13 = new SQLiteCommand(con);
                cmd13.CommandType = CommandType.Text;
                cmd13.CommandText = "INSERT into yearly(workerid) VALUES(@wwcode)";
                cmd13.Parameters.Add(new SQLiteParameter("@wwcode", wcode));
                cmd13.ExecuteNonQuery();
                SQLiteCommand command = new SQLiteCommand(con);
                command.CommandText = "INSERT into users(workerid,name,surname,status,working) VALUES(@wcode,@wname,@wsname,@status,@working)";
                command.CommandType = CommandType.Text;
                command.Parameters.Add(new SQLiteParameter("@wcode", wcode));
                command.Parameters.Add(new SQLiteParameter("@wname", wname));
                command.Parameters.Add(new SQLiteParameter("@wsname", wsname));
                command.Parameters.Add(new SQLiteParameter("@status", wstatus));
                command.Parameters.Add(new SQLiteParameter("@working", wwork));
                command.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception ex)
            {

                MessageBox.Show("Cannot add employee. Please try again.");
            }
        }

        private void addUsersToolStripMenuItem_Click(object sender, EventArgs e)
        {
            tabControl1.SelectTab(tabOne);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            string sql = "SELECT * From users ORDER By workerid ASC";
            SQLiteCommand command = new SQLiteCommand(sql, con);
            SQLiteDataAdapter da = new SQLiteDataAdapter(command);
            DataSet ds = new DataSet();
            da.Fill(ds);
            DataTable dt = ds.Tables[0];
            this.grid2.DataSource = dt;
            try
            {
                grid2.Columns.Remove("Column1");
                grid2.Columns.Remove("Column2");
                grid2.Columns.Remove("Column3");
            }
            catch (Exception ex)
            {
            }
            con.Close();
        }
        private bool checkLeap(int year)
        {
            bool isleap = false;
            if (year % 400 == 0)
                isleap = true;
            else
                if (year % 100 == 0)
                    isleap = false;
                else if (year % 4 == 0)
                    isleap = true;
                else
                    isleap = false;
            return isleap;
        }
        private void InitializeGrid()
        {
            for (int i = 1; i <= MaxColumns; i++)
            {
                grid2.Columns.Add(
                    String.Format("Column{0}", i),
                    String.Format("Column{0}", i));
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {

            pbxGreen.Visible = false;
            OpenFileDialog file = new OpenFileDialog();
            file.Filter = "All files (*.*)|*.*|All files (*.*)|*.*";
            file.Title = "Select a CSV";
            if (file.ShowDialog(this) == DialogResult.OK)
            {
                ReadFile(file.FileName);
                pbxGreen.Visible = true;
            }
            textBox1.Text = file.FileName;
            string a, b, c;
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            progressBar1.Visible = true;
            timer1.Enabled = true;
            grid2.Columns[0].HeaderText = "Employee Number";
            grid2.Columns[1].HeaderText = "Employee Surname";
            grid2.Columns[2].HeaderText = "Employee Name";
            try
            {
                for (int rows = 0; rows < grid2.Rows.Count - 1; rows++)
                {
                    SQLiteCommand command = new SQLiteCommand(con);
                    a = grid2.Rows[rows].Cells[0].Value.ToString();
                    b = grid2.Rows[rows].Cells[1].Value.ToString();
                    c = grid2.Rows[rows].Cells[2].Value.ToString();
                    command.CommandText = "INSERT into users(workerid,name,surname) VALUES(@wcode,@wname,@wsname)";
                    SQLiteCommand cmd1 = new SQLiteCommand(con);
                    cmd1.CommandText = "INSERT into yearly(workerid) VALUES(@wid)";
                    cmd1.CommandType = CommandType.Text;
                    cmd1.Parameters.Add(new SQLiteParameter("@wid", a));
                    cmd1.ExecuteNonQuery();
                    command.CommandType = CommandType.Text;
                    command.Parameters.Add(new SQLiteParameter("@wcode", a));
                    command.Parameters.Add(new SQLiteParameter("@wname", c));
                    command.Parameters.Add(new SQLiteParameter("@wsname", b));

                    command.ExecuteNonQuery();

                }

                con.Close();
            }
            catch (Exception ex)
            {
                pbxRed.Visible = true;
            }
        }

        private bool ReadFile(string filename)
        {
            Cursor = Cursors.WaitCursor;

            try
            {
                grid2.Rows.Clear();
                List<string> columns = new List<string>();
                using (var reader = new CsvFileReader(filename))
                {
                    while (reader.ReadRow(columns))
                    {
                        grid2.Rows.Add(columns.ToArray());

                    }
                }
                FileName = filename;
                Modified = false;

                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(String.Format("Error reading from {0}.\r\n\r\n{1}", filename, ex.Message));
            }
            finally
            {
                Cursor = Cursors.Default;
            }
            return false;
        }

        private void ReadValues()
        {
            List<string> columns = new List<string>();
            using (var reader = new CsvFileReader("ReadTest.csv"))
            {
                while (reader.ReadRow(columns))
                {
                    // TODO: Do something with columns' values
                }
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void importToolStripMenuItem_Click(object sender, EventArgs e)
        {
            tabControl1.SelectTab(tabImport);
        }

        private void exportToolStripMenuItem_Click(object sender, EventArgs e)
        {


        }
        private int GetMaxColumnUsed()
        {
            int maxColumnUsed = 0;
            foreach (DataGridViewRow row in grid3.Rows)
            {
                if (!row.IsNewRow)
                {
                    for (int col = row.Cells.Count - 1; col >= 0; col--)
                    {
                        if (row.Cells[col].Value != null)
                        {
                            if (maxColumnUsed < (col + 1))
                                maxColumnUsed = (col + 1);
                            continue;
                        }
                    }
                }
            }
            return maxColumnUsed;
        }
        private bool WriteFile(string filename)
        {
            Cursor = Cursors.WaitCursor;
            try
            {
                // Like Excel, we'll get the highest column number used,
                // and then write out that many columns for every row
                int numColumns = GetMaxColumnUsed();
                using (var writer = new CsvFileWriter(filename))
                {
                    foreach (DataGridViewRow row in grid3.Rows)
                    {
                        if (!row.IsNewRow)
                        {
                            List<string> columns = new List<string>();
                            for (int col = 0; col < numColumns; col++)
                                columns.Add((string)row.Cells[col].Value.ToString());
                            writer.WriteRow(columns);
                        }
                    }
                }
                FileName = filename;
                Modified = false;
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(String.Format("Error writing to {0}.\r\n\r\n{1}", filename, ex.Message));
            }
            finally
            {
                Cursor = Cursors.Default;
            }
            return false;
        }

        private void getLeave(string workerid, Label sick, Label paid, Label fam, Label mon, Label fri)
        {
            /* SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
             con.Open();
             SQLiteCommand monc = new SQLiteCommand(con);
             monc.CommandText = "";
             monc.ExecuteNonQuery();
             return 5;*/
            int retval = 0;
            string retval1 = "";
            int famresp = 0;
            string famrespon1 = "";
            int paidleave = 0;
            string paidleave1 = "";
            int frileave = 0;
            string frileave1 = "";
            int monleave = 0;
            string monleave1 = "";
            SQLiteDataReader dr;
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            string query = "SELECT totalsickleave FROM users WHERE workerid = '" + workerid + "'";
            string query2 = "SELECT totalfamrespon FROM users WHERE workerid = '" + workerid + "'";
            string query3 = "SELECT totalpaidleave FROM users WHERE workerid = '" + workerid + "'";
            string query4 = "SELECT totalmonday FROM users WHERE workerid = '" + workerid + "'";
            string query5 = "SELECT totalfriday FROM users WHERE workerid = '" + workerid + "'";
            SQLiteCommand cmd = new SQLiteCommand(query, con);
            SQLiteCommand cmd1 = new SQLiteCommand(query2, con);
            SQLiteCommand cmd2 = new SQLiteCommand(query3, con);
            SQLiteCommand cmd3 = new SQLiteCommand(query4, con);
            SQLiteCommand cmd4 = new SQLiteCommand(query5, con);
            //Reading totalsickleave
            SQLiteDataReader myReader = cmd.ExecuteReader();
            // Run through the results
            while (myReader.Read())
            {
                retval1 = myReader[0].ToString(); ;
                // or alternatively:
                // string fname2 = myReader["FirstName"];
                // Either of these should work
            }
            retval = int.Parse(retval1);
            myReader.Close();
            //End SickLeave Reader

            //Reading total fam responsibility
            SQLiteDataReader famreader = cmd1.ExecuteReader();
            // Run through the results
            while (famreader.Read())
            {
                famrespon1 = famreader[0].ToString();
                // or alternatively:
                // string fname2 = myReader["FirstName"];
                // Either of these should work
            }
            famresp = int.Parse(famrespon1);
            famreader.Close();
            //End fam respon Reader

            //Reading total paid leave
            SQLiteDataReader paidreader = cmd2.ExecuteReader();
            // Run through the results
            while (paidreader.Read())
            {
                paidleave1 = paidreader[0].ToString();
                // or alternatively:
                // string fname2 = myReader["FirstName"];
                // Either of these should work
            }
            paidleave = int.Parse(paidleave1);
            paidreader.Close();
            //End paid leave reader

            //Reading total friday leave
            SQLiteDataReader monreader = cmd4.ExecuteReader();
            // Run through the results
            while (monreader.Read())
            {
                monleave1 = monreader[0].ToString();
                // or alternatively:
                // string fname2 = myReader["FirstName"];
                // Either of these should work
            }
            monleave = int.Parse(monleave1);
            monreader.Close();
            //End friday leave reader

            //Reading total monday leave
            SQLiteDataReader frireader = cmd3.ExecuteReader();
            // Run through the results
            while (frireader.Read())
            {
                frileave1 = frireader[0].ToString();
                // or alternatively:
                // string fname2 = myReader["FirstName"];
                // Either of these should work
            }
            frileave = int.Parse(frileave1);
            frireader.Close();
            //End monday leave reader
            con.Close();
            sick.Text = "Total Sick Leave: " + retval + "/30";
            fam.Text = "Total Family Respon Leave: " + famresp + "/3";
            paid.Text = "Total Paid Leave: " + paidleave + "/15";
            fri.Text = "Total Fridays Absent: " + frileave;
            mon.Text = "Total Mondays Absent: " + monleave;

            if (paidleave == 15)
            {
                btnUpdateE.Enabled = false;
                MessageBox.Show("Paid Leave Full");
            }
            if (famresp == 3)
            {
                btnUpdateE.Enabled = false;
                MessageBox.Show("Fam Resp Full");
            }
            if (retval == 30)
            {
                btnUpdateE.Enabled = false;
                MessageBox.Show("Sick Leave Full");
            }

            if ((frileave > 2) && (monleave > 2))
            {

                pwarnmsg = "More than 2 days absent on both a Friday and Monday";
                startTimerWarn();
            }
            else
                if (frileave > 2)
                {

                    pwarnmsg = "Absent for more than 2 Fridays";
                    startTimerWarn();
                }
                else
                    if (monleave > 2)
                    {
                        pwarnmsg = "Absent for more than 2 Mondays";
                        startTimerWarn();
                    }


        }
        private void startTimerWarn()
        {
            lblWarn.Text = pwarnmsg;
            picwarn.Visible = true;
            timerpic.Enabled = true;
        }
        private string warnMessage(string message)
        {
            return message;
        }
        private void button3_Click(object sender, EventArgs e)
        {
            int day = int.Parse(dtmUpdate.Value.Day.ToString());
            int month = int.Parse(dtmUpdate.Value.Month.ToString());
            int year = int.Parse(dtmUpdate.Value.Year.ToString());
            bool leap = checkLeap(year);
            int days = checkDays(month, leap);
            int[] dayarr = new int[7];
            int[] montharr = new int[7];
            int[] yeararr = new int[7];
            int dcounter = 0;
            if ((day + 7) > days)
            {
                for (int x = day; x <= days; x++)
                {
                    dayarr[dcounter] = x;
                    montharr[dcounter] = month;
                    yeararr[dcounter] = year;
                    dcounter++;
                }
                for (int y = 1; y <= (6 - (days - day)); y++)
                {
                    dayarr[dcounter] = y;
                    if (month == 12)
                    {
                        montharr[dcounter] = 1;
                        yeararr[dcounter] = year + 1;
                    }
                    else
                    {
                        yeararr[dcounter] = year;
                        montharr[dcounter] = month + 1;
                    }

                    dcounter++;
                }
            }
            else
            {
                for (int x = day; x <= day + 6; x++)
                {
                    dayarr[dcounter] = x;
                    montharr[dcounter] = month;
                    yeararr[dcounter] = year;
                    dcounter++;
                }
            }
            string cool = "";
            /*  for (int z = 0; z <= 6; z++)
              {
                  cool = cool + montharr[z] + "\n";
              }
              MessageBox.Show(cool);
             */
            //test
            if (firstupdate == "")
                firstupdate = "01/01/2000";
            DateTime first = DateTime.ParseExact(firstupdate, "dd/MM/yyyy", null);
            string firstup = first.ToString("dd/MM/yyyy");
            if (lastupdate == "")
                lastupdate = "02/01/2000";
            DateTime last = DateTime.ParseExact(lastupdate, "dd/MM/yyyy", null);
            string lastup = last.ToString("dd/MM/yyyy");
            int result = 0;
            bool update = false;
            string comp1 = "";
            bool isfirstupdate = false;
            int isff = 0;
            for (int z = 6; z >= 0; z--)
            {
                if ((dayarr[z] < 10) && (montharr[z] < 10))
                {
                    comp1 = "0" + dayarr[z].ToString() + "/0" + montharr[z].ToString() + "/" + yeararr[z].ToString();

                }
                else
                    if (dayarr[z] < 10)
                    {
                        comp1 = "0" + dayarr[z].ToString() + "/" + montharr[z].ToString() + "/" + yeararr[z].ToString();

                    }
                    else
                        if (montharr[z] < 10)
                        {
                            comp1 = dayarr[z].ToString() + "/0" + montharr[z].ToString() + "/" + yeararr[z].ToString();

                        }
                        else
                        {
                            comp1 = dayarr[z].ToString() + "/" + montharr[z].ToString() + "/" + yeararr[z].ToString();
                        }

                result = DateTime.Compare(DateTime.ParseExact(comp1, "dd/MM/yyyy", null), first);
            }
            string isf = returnproperdate(dayarr[0].ToString() + "/" + montharr[0].ToString() + "/" + yeararr[0].ToString());
            DateTime isfd = DateTime.ParseExact(isf, "dd/MM/yyyy", null);
            isff = DateTime.Compare(isfd, first);
            if (isff < 0)
                isfirstupdate = true;
            else
                if (isff == 0)
                    isfirstupdate = false;
                else
                    if (isff > 0)
                        isfirstupdate = false;
                    else isfirstupdate = false;

            if (result < 0)
            {
                update = false;

            }
            else
                if (result == 0)
                {
                    update = false;
                }
                else
                    if (result > 0)
                    {

                        for (int x = 6; x >= 0; x--)
                        {
                            if ((dayarr[x] < 10) && (montharr[x] < 10))
                                comp1 = "0" + dayarr[x].ToString() + "/0" + montharr[x].ToString() + "/" + yeararr[x].ToString();
                            else
                                if (dayarr[x] < 10)
                                    comp1 = "0" + dayarr[x].ToString() + "/" + montharr[x].ToString() + "/" + yeararr[x].ToString();
                                else
                                    if (montharr[x] < 10)
                                        comp1 = dayarr[x].ToString() + "/0" + montharr[x].ToString() + "/" + yeararr[x].ToString();
                                    else comp1 = dayarr[x].ToString() + "/" + montharr[x].ToString() + "/" + yeararr[x].ToString();
                            result = DateTime.Compare(DateTime.ParseExact(comp1, "dd/MM/yyyy", null), last);
                        }
                    }
            if (result < 0)
            {
                update = false;

            }
            else
                if (result == 0)
                {
                    update = false;

                }
                else
                    update = true;

            //end test
            ButtonOnclickUpdate();
            lblMon.Text = dayarr[0].ToString() + "/" + montharr[0].ToString() + "/" + yeararr[0].ToString();
            lblTue.Text = dayarr[1].ToString() + "/" + montharr[1].ToString() + "/" + yeararr[1].ToString();
            lblWed.Text = dayarr[2].ToString() + "/" + montharr[2].ToString() + "/" + yeararr[2].ToString();
            lblThur.Text = dayarr[3].ToString() + "/" + montharr[3].ToString() + "/" + yeararr[3].ToString();
            lblFri.Text = dayarr[4].ToString() + "/" + montharr[4].ToString() + "/" + yeararr[4].ToString();
            lblSat.Text = dayarr[5].ToString() + "/" + montharr[5].ToString() + "/" + yeararr[5].ToString();
            lblSun.Text = dayarr[6].ToString() + "/" + montharr[6].ToString() + "/" + yeararr[6].ToString();
            //comparing date
            DateTime datemon = new DateTime(yeararr[0], montharr[0], dayarr[0]);
            DateTime datesun = new DateTime(yeararr[6], montharr[6], dayarr[6]);
            //end date compare
            // if (update == true)
            //  {
            string moncol = lblMon.Text;
            string tuecol = lblTue.Text;
            string wedcol = lblWed.Text;
            string thurcol = lblThur.Text;
            string fricol = lblFri.Text;
            string satcol = lblSat.Text;
            string suncol = lblSun.Text;
            //start changing values for moncolumn
            string text = moncol;
            string[] bits = text.Split('/');
            if ((int.Parse(bits[0]) < 10) && (int.Parse(bits[1]) < 10) && (int.Parse(bits[2]) < 10))
                text = "0" + bits[0].ToString() + "/0" + bits[1].ToString() + "/0" + bits[2].ToString();
            else
                if ((int.Parse(bits[0]) < 10) && (int.Parse(bits[1]) < 10))
                    text = "0" + bits[0].ToString() + "/0" + bits[1].ToString() + "/" + bits[2].ToString();
                else
                    if ((int.Parse(bits[0]) < 10) && (int.Parse(bits[2]) < 10))
                        text = "0" + bits[0].ToString() + "/" + bits[1].ToString() + "/0" + bits[2].ToString();
                    else
                        if ((int.Parse(bits[1]) < 10) && (int.Parse(bits[2]) < 10))
                            text = bits[0].ToString() + "/0" + bits[1].ToString() + "/0" + bits[2].ToString();
                        else
                            if (int.Parse(bits[0]) < 10)
                                text = "0" + bits[0].ToString() + "/" + bits[1].ToString() + "/" + bits[2].ToString();
                            else
                                if (int.Parse(bits[1]) < 10)
                                    text = bits[0].ToString() + "/0" + bits[1].ToString() + "/" + bits[2].ToString();
                                else
                                    if (int.Parse(bits[2]) < 10)
                                        text = bits[0].ToString() + "/" + bits[1].ToString() + "/0" + bits[2].ToString();
            //end changing values for mon column
            //////////////////////////////////////////////
            //start changing columns for sunday or last
            string text2 = suncol;
            string[] bits2 = text2.Split('/');
            if ((int.Parse(bits2[0]) < 10) && (int.Parse(bits2[1]) < 10) && (int.Parse(bits2[2]) < 10))
                text2 = "0" + bits2[0].ToString() + "/0" + bits2[1].ToString() + "/0" + bits2[2].ToString();
            else
                if ((int.Parse(bits2[0]) < 10) && (int.Parse(bits2[1]) < 10))
                    text2 = "0" + bits2[0].ToString() + "/0" + bits2[1].ToString() + "/" + bits2[2].ToString();
                else
                    if ((int.Parse(bits2[0]) < 10) && (int.Parse(bits2[2]) < 10))
                        text2 = "0" + bits2[0].ToString() + "/" + bits2[1].ToString() + "/0" + bits2[2].ToString();
                    else
                        if ((int.Parse(bits2[1]) < 10) && (int.Parse(bits2[2]) < 10))
                            text2 = bits2[0].ToString() + "/0" + bits2[1].ToString() + "/0" + bits2[2].ToString();
                        else
                            if (int.Parse(bits2[0]) < 10)
                                text2 = "0" + bits2[0].ToString() + "/" + bits2[1].ToString() + "/" + bits2[2].ToString();
                            else
                                if (int.Parse(bits2[1]) < 10)
                                    text2 = bits2[0].ToString() + "/0" + bits2[1].ToString() + "/" + bits2[2].ToString();
                                else
                                    if (int.Parse(bits2[2]) < 10)
                                        text2 = bits2[0].ToString() + "/" + bits2[1].ToString() + "/0" + bits2[2].ToString();

            //end changing columns for sunday or last
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            SQLiteCommand monc = new SQLiteCommand(con);
            monc.CommandText = "ALTER TABLE main.yearly ADD COLUMN '" + returnproperdate(moncol) + "' VARCHAR";
            monc.ExecuteNonQuery();
            /*   if (monc.ExecuteNonQuery() < 1)
            {
                monc.CommandText = "ALTER TABLE main.yearly ADD COLUMN '" + moncol + "' VARCHAR";
                monc.ExecuteNonQuery();
            }*/
            SQLiteCommand tuec = new SQLiteCommand(con);
            tuec.CommandText = "ALTER TABLE main.yearly ADD COLUMN '" + returnproperdate(tuecol) + "' VARCHAR";
            tuec.ExecuteNonQuery();
            SQLiteCommand wedc = new SQLiteCommand(con);
            wedc.CommandText = "ALTER TABLE main.yearly ADD COLUMN '" + returnproperdate(wedcol) + "' VARCHAR";
            wedc.ExecuteNonQuery();
            SQLiteCommand thurc = new SQLiteCommand(con);
            thurc.CommandText = "ALTER TABLE main.yearly ADD COLUMN '" + returnproperdate(thurcol) + "' VARCHAR";
            thurc.ExecuteNonQuery();
            SQLiteCommand fric = new SQLiteCommand(con);
            fric.CommandText = "ALTER TABLE main.yearly ADD COLUMN '" + returnproperdate(fricol) + "' VARCHAR";
            fric.ExecuteNonQuery();
            SQLiteCommand satc = new SQLiteCommand(con);
            satc.CommandText = "ALTER TABLE main.yearly ADD COLUMN '" + returnproperdate(satcol) + "' VARCHAR";
            satc.ExecuteNonQuery();
            SQLiteCommand sunc = new SQLiteCommand(con);
            sunc.CommandText = "ALTER TABLE main.yearly ADD COLUMN '" + returnproperdate(suncol) + "' VARCHAR";
            sunc.ExecuteNonQuery();
            con.Close();
            //    button3.Enabled = false;
            //   dtmUpdate.Enabled = false;

            /*  if (firstupdate != "")
              {
                  firstupdate = text;
                  lastupdate = text2;
              }*/

            if (isfirstupdate == true)
            {
                firstupdate = text;
                lastupdate = text2;
            }
            else
                lastupdate = text2;
            WriteData(ref firstupdate, ref lastupdate);
            setMaxmin();
            //  }
            // else
            // {
            // MessageBox.Show("You have previously updated");

            //  }
            //
        }//end button click

        //coolfunc
        private string returnproperdate(string givedate1)
        {
            string t = givedate1;
            string givedate = "";
            string[] tt = t.Split('/');
            if ((int.Parse(tt[0]) < 10) && (int.Parse(tt[1]) < 10))
                givedate = "0" + tt[0].ToString() + "/0" + tt[1].ToString() + "/" + tt[2].ToString();
            else
                if (int.Parse(tt[0]) < 10)
                    givedate = "0" + tt[0].ToString() + "/" + tt[1].ToString() + "/" + tt[2].ToString();
                else
                    if (int.Parse(tt[1]) < 10)
                        givedate = tt[0].ToString() + "/0" + tt[1].ToString() + "/" + tt[2].ToString();
                    else givedate = tt[0].ToString() + "/" + tt[1].ToString() + "/" + tt[2].ToString();

            return givedate;
        }
        //coolfunc
        private int checkDays(int month, bool leap)
        {
            if (leap == false)
            {
                switch (month)
                {
                    case 1: return 31;
                    case 2: return 28;
                    case 3: return 31;
                    case 4: return 30;
                    case 5: return 31;
                    case 6: return 30;
                    case 7: return 31;
                    case 8: return 31;
                    case 9: return 30;
                    case 10: return 31;
                    case 11: return 30;
                    case 12: return 31;
                    default: return 0;
                } //end switch month

            }//end if leap is flase
            else
            {
                switch (month)
                {
                    case 1: return 31;
                    case 2: return 29;
                    case 3: return 31;
                    case 4: return 30;
                    case 5: return 31;
                    case 6: return 30;
                    case 7: return 31;
                    case 8: return 31;
                    case 9: return 30;
                    case 10: return 31;
                    case 11: return 30;
                    case 12: return 31;
                    default: return 0;
                } //end switch month
            }//end else 
        }

        private string getType(ComboBox cbx)
        {
            int iex = cbx.SelectedIndex;
            switch (iex)
            {
                case 0: return "ATTENDED";
                case 1: return "PSC";
                case 2: return "UL";
                case 3: return "PL";
                case 4: return "PFR";
                case 5: return "UFR";
                case 6: return "USC";
                case 7: return "AW";
                default: return "ATTENDED";
            }
        }
        private void updateLog(ref string log, string mon, string tue, string wed, string thur, string fri, string sat, string sun)
        {
            log += "-=-=-=-=-=-=-=-=-=-=-\n";
            log += "Employee Number: " + cbxENumber.SelectedText + "\n";
            log += "Name: " + edtEName.Text + "\n";
            log += "Surname: " + edtESurname.Text + "\n";
            log += "Monday: " + mon + "\n";
            log += "Tuesday: " + tue + "\n";
            log += "Wednesday: " + wed + "\n";
            log += "Thursday: " + thur + "\n";
            log += "Friday: " + fri + "\n";
            log += "Saturday: " + sat + "\n";
            log += "Sunday: " + sun + "\n";
            log += "-=-=-=-=-=-=-=-=-=-=-\n";
        }

        private void button4_Click(object sender, EventArgs e)
        {
            if (cbxENumber.Text != "")
            {
                if (MessageBox.Show("Are you sure you want to update this employee? Is all the information correct?", "Employee Update", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    string mon = getType(cbxMon);
                    string tue = getType(cbxTue);
                    string wed = getType(cbxWed);
                    string thur = getType(cbxThur);
                    string fri = getType(cbxFri);
                    string sat = getType(cbxSat);
                    string sun = getType(cbxSun);
                    string workerid = cbxENumber.Text;
                    string moncol = lblMon.Text;
                    string tuecol = lblTue.Text;
                    string wedcol = lblWed.Text;
                    string thurcol = lblThur.Text;
                    string fricol = lblFri.Text;
                    string satcol = lblSat.Text;
                    string suncol = lblSun.Text;
                    int totfri = 0;
                    int totmon = 0;
                    if (mon != "ATTENDED")
                        totmon = 1;
                    if (fri != "ATTENDED")
                        totfri = 1;
                    updateLog(ref log, mon, tue, wed, thur, fri, sat, sun);
                    richTextBox1.Text += log;
                    //   double 
                    //Update Worker to database
                    SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
                    con.Open();
                    SQLiteCommand cmd12 = new SQLiteCommand(con);
                    cmd12.CommandType = CommandType.Text;
                    cmd12.CommandText = "DELETE From weekly";
                    cmd12.ExecuteNonQuery();
                    SQLiteCommand command = new SQLiteCommand(con);
                    command.CommandText = "INSERT into weekly(workerid,mon,tue,wed,thur,fri,sat,sun) VALUES(@wid,@mon,@tue,@wed,@thur,@fri,@sat,@sun)";
                    command.CommandType = CommandType.Text;
                    command.Parameters.Add(new SQLiteParameter("@wid", workerid));
                    command.Parameters.Add(new SQLiteParameter("@mon", mon));
                    command.Parameters.Add(new SQLiteParameter("@tue", tue));
                    command.Parameters.Add(new SQLiteParameter("@wed", wed));
                    command.Parameters.Add(new SQLiteParameter("@thur", thur));
                    command.Parameters.Add(new SQLiteParameter("@fri", fri));
                    command.Parameters.Add(new SQLiteParameter("@sat", sat));
                    command.Parameters.Add(new SQLiteParameter("@sun", sun));
                    command.ExecuteNonQuery();

                    SQLiteCommand newcmd = new SQLiteCommand(con);
                    newcmd.CommandText = "UPDATE users SET totalfriday = (totalfriday + '" + totfri + "') WHERE workerid = @workerid";
                    newcmd.Parameters.Add(new SQLiteParameter("@workerid", workerid));
                    newcmd.ExecuteNonQuery();
                    SQLiteCommand newcmd1 = new SQLiteCommand(con);
                    newcmd1.CommandText = "UPDATE users SET totalmonday = (totalmonday + '" + totmon + "') WHERE workerid = @workerid";
                    newcmd1.Parameters.Add(new SQLiteParameter("@workerid", workerid));
                    newcmd1.ExecuteNonQuery();

                    SQLiteCommand insertUserYearly = new SQLiteCommand(con);
                    insertUserYearly.CommandText = "UPDATE yearly SET'" + returnproperdate(moncol) + "' = @ones,'" + returnproperdate(tuecol) + "' = @twos,'" + returnproperdate(wedcol) + "'=@threes,'" + returnproperdate(thurcol) + "' = @fours,'" + returnproperdate(fricol) + "' = @fives,'" + returnproperdate(satcol) + "' = @sixs,'" + returnproperdate(suncol) + "' = @sevens WHERE workerid = @workerid";
                    insertUserYearly.CommandType = CommandType.Text;
                    //insertUserYearly.Parameters.Add(new SQLiteParameter("@one", moncol));
                    insertUserYearly.Parameters.Add(new SQLiteParameter("@ones", mon));
                    // insertUserYearly.Parameters.Add(new SQLiteParameter("@two", tuecol));
                    insertUserYearly.Parameters.Add(new SQLiteParameter("@twos", tue));
                    //insertUserYearly.Parameters.Add(new SQLiteParameter("@three", wedcol));
                    insertUserYearly.Parameters.Add(new SQLiteParameter("@threes", wed));
                    //insertUserYearly.Parameters.Add(new SQLiteParameter("@four", thurcol));
                    insertUserYearly.Parameters.Add(new SQLiteParameter("@fours", thur));
                    //insertUserYearly.Parameters.Add(new SQLiteParameter("@five", fricol));
                    insertUserYearly.Parameters.Add(new SQLiteParameter("@fives", fri));
                    //insertUserYearly.Parameters.Add(new SQLiteParameter("@six", satcol));
                    insertUserYearly.Parameters.Add(new SQLiteParameter("@sixs", sat));
                    //insertUserYearly.Parameters.Add(new SQLiteParameter("@seven", suncol));
                    insertUserYearly.Parameters.Add(new SQLiteParameter("@sevens", sun));
                    insertUserYearly.Parameters.Add(new SQLiteParameter("@workerid", workerid));
                    insertUserYearly.ExecuteNonQuery();
                    con.Close();
                }
            }
        }
        public static string WorkerInfo2(string Query, TextBox Texthere)
        {
            string retval = "";
            int retval2 = 0;
            SQLiteDataReader dr;
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(Query, con);
            SQLiteDataReader myReader = cmd.ExecuteReader();

            // Run through the results
            while (myReader.Read())
            {
                retval = myReader.GetValue(0).ToString(); ;
                // or alternatively:
                // string fname2 = myReader["FirstName"];
                // Either of these should work
            }

            myReader.Close();
            con.Close();
            return retval;
        }

        public static string WorkerInfo(string Query, TextBox Texthere)
        {
            string retval = "";
            SQLiteDataReader dr;
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(Query, con);
            SQLiteDataReader myReader = cmd.ExecuteReader();

            // Run through the results
            while (myReader.Read())
            {
                retval = myReader.GetString(0);
                // or alternatively:
                // string fname2 = myReader["FirstName"];
                // Either of these should work
            }

            myReader.Close();
            con.Close();
            return retval;
        }

        public static string GetItem(string Query)
        {
            string retval = "";
            SQLiteDataReader dr;
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(Query, con);
            SQLiteDataReader myReader = cmd.ExecuteReader();

            // Run through the results
            while (myReader.Read())
            {
                retval = myReader[0].ToString(); ;
                // or alternatively:
                // string fname2 = myReader["FirstName"];
                // Either of these should work
            }

            myReader.Close();
            con.Close();
            return retval;
        }

        private void cbxENumber_SelectedIndexChanged(object sender, EventArgs e)
        {
            btnUpdateE.Enabled = true;
            picwarn.Visible = false;
            lblWarn.Text = "";
            timerpic.Enabled = false;
            string workerinfo = cbxENumber.Text;
            string q1 = "SELECT name from users WHERE workerid = '" + workerinfo + "'";
            string q2 = "SELECT surname from users WHERE workerid = '" + workerinfo + "'";
            edtEName.Text = WorkerInfo(q1, edtEName);
            edtESurname.Text = WorkerInfo(q2, edtESurname);
            getLeave(workerinfo, lblSickLeave, lblPaidLeave, lblFam, lblmonday, lblfriday);
            //     lblSickLeave.Text = "Total Sick Leave Left: " + getTotalSickLeave(cbxENumber.Text);
            //     lblPaidLeave.Text = "Total Paid Leave Left: " + getTotalPaidLeave(cbxENumber.Text);
            //     lblFamResp.Text = "Total Family Responsibility Left: " + getTotalFamResp(cbxENumber.Text);
        }

        private void tabUpdate_Click(object sender, EventArgs e)
        {

        }

        private void button4_Click_1(object sender, EventArgs e)
        {
            string query = "SELECT workerid FROM users";
            FillDropDownList(query, cbxENumber);
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            progressBar1.Visible = true;
            while (progressBar1.Value < 100)
            {
                progressBar1.Value += 1;
            }
            timer1.Enabled = false;
            progressBar1.Visible = false;
        }

        private void button5_Click(object sender, EventArgs e)
        {

        }

        private void button5_Click_1(object sender, EventArgs e)
        {
            if (cbxType.SelectedIndex == 0)
            {
                SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
                con.Open();
                string sql = "SELECT users.name, users.surname,users.totalfriday,users.totalmonday,yearly.*  FROM yearly JOIN users WHERE yearly.workerid = users.workerid ORDER By workerid ASC";
                SQLiteCommand command = new SQLiteCommand(sql, con);
                SQLiteDataAdapter da = new SQLiteDataAdapter(command);
                DataSet ds = new DataSet();

                da.Fill(ds);
                DataTable dt = ds.Tables[0];
                this.grid3.DataSource = dt;
                con.Close();
            }
            else
                if (cbxType.SelectedIndex == 1)
                {
                    SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
                    con.Open();
                    string sql = "SELECT users.name, users.surname,users.totalfriday,users.totalmonday,yearly.*  FROM yearly JOIN users WHERE ((yearly.workerid = users.workerid) AND (users.working = 1)) ORDER By workerid ASC";
                    SQLiteCommand command = new SQLiteCommand(sql, con);
                    SQLiteDataAdapter da = new SQLiteDataAdapter(command);
                    DataSet ds = new DataSet();

                    da.Fill(ds);
                    DataTable dt = ds.Tables[0];
                    this.grid3.DataSource = dt;
                    con.Close();
                }
                else
                    if (cbxType.SelectedIndex == 2)
                    {
                        SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
                        con.Open();
                        string sql = "SELECT users.name, users.surname,users.terminationreason,users.totalfriday,users.totalmonday,yearly.*  FROM yearly JOIN users WHERE ((yearly.workerid = users.workerid) AND (users.working = 0)) ORDER By workerid ASC";
                        SQLiteCommand command = new SQLiteCommand(sql, con);
                        SQLiteDataAdapter da = new SQLiteDataAdapter(command);
                        DataSet ds = new DataSet();

                        da.Fill(ds);
                        DataTable dt = ds.Tables[0];
                        this.grid3.DataSource = dt;
                        con.Close();
                    }


        }

        private void administratorToolStripMenuItem_Click(object sender, EventArgs e)
        {
            string input = Microsoft.VisualBasic.Interaction.InputBox("Prompt",
                       "Enter admin password.",
                       "",
                       0,
                       0);
            if (btnGenTables.Visible == true)
            {
                btnGenTables.Visible = false;
            }
            else
                if (input == "crhafrica1")
                {
                    btnGenTables.Visible = true;
                }
        }

        private void btnGenTables_Click(object sender, EventArgs e)
        {
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            string sql = "DROP Table yearly";
            SQLiteCommand command = new SQLiteCommand(sql, con);
            command.ExecuteNonQuery();
            string sql1 = "CREATE  TABLE main.yearly (workerid  PRIMARY KEY  NOT NULL  UNIQUE )";
            SQLiteCommand co1 = new SQLiteCommand(sql1, con);
            co1.ExecuteNonQuery();
            string sql2 = "INSERT into yearly(workerid) select workerid FROM users";
            SQLiteCommand co = new SQLiteCommand(sql2, con);
            co.ExecuteNonQuery();
            con.Close();
            string fi = "01/01/2000";
            string la = "02/01/2000";
            lastupdate = la;
            firstupdate = fi;
            WriteData(ref fi, ref la);
            button3.Enabled = true;
            dtmUpdate.Enabled = true;
            InitializeUpdate();
        }

        private void terminateEmployeeToolStripMenuItem_Click(object sender, EventArgs e)
        {

        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            string workerinfo = comboBox1.Text;
            string q1 = "SELECT name from users WHERE workerid = '" + workerinfo + "'";
            string q2 = "SELECT surname from users WHERE workerid = '" + workerinfo + "'";
            edtTN.Text = WorkerInfo(q1, edtTN);
            edtTS.Text = WorkerInfo(q2, edtTS);
        }

        private void button7_Click(object sender, EventArgs e)
        {
            if (textBox2.Text != "")
            {
                string q1 = "UPDATE users SET working = 0, terminationreason = '" + textBox2.Text + "' WHERE workerid = '" + comboBox1.Text + "'";
                SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
                con.Open();
                SQLiteCommand command = new SQLiteCommand(q1, con);
                command.ExecuteNonQuery();

                string sql = "SELECT users.name, users.surname,users.terminationreason  FROM users WHERE users.working = 0 ORDER By workerid ASC";
                SQLiteCommand cmd = new SQLiteCommand(sql, con);
                SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                DataSet ds = new DataSet();

                da.Fill(ds);
                DataTable dt = ds.Tables[0];
                this.grid4.DataSource = dt;
                con.Close();
                label14.Text = "Success";
            }
            else
                label14.Text = "Please enter a reason";

        }

        private void button8_Click(object sender, EventArgs e)
        {
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            string sql = "SELECT users.name, users.surname,users.terminationreason  FROM users WHERE users.working = 0 ORDER By surname ASC";
            SQLiteCommand cmd = new SQLiteCommand(sql, con);
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
            DataSet ds = new DataSet();

            da.Fill(ds);
            DataTable dt = ds.Tables[0];
            this.grid4.DataSource = dt;
            con.Close();
        }

        private void tab_Click(object sender, EventArgs e)
        {

        }

        private void button10_Click(object sender, EventArgs e)
        {
            string query = "SELECT workerid FROM users WHERE working = 0;";
            FillDropDownList(query, cbxRein);
        }

        private void button6_Click(object sender, EventArgs e)
        {
            string query = "SELECT workerid FROM users WHERE working = 1;";
            FillDropDownList(query, comboBox1);
        }

        private void button9_Click(object sender, EventArgs e)
        {
            string q1 = "UPDATE users SET working = 1, terminationreason = '' WHERE workerid = '" + cbxRein.Text + "'";
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            SQLiteCommand command = new SQLiteCommand(q1, con);
            command.ExecuteNonQuery();

            string sql = "SELECT users.name, users.surname,users.terminationreason  FROM users WHERE users.working = 0 ORDER By workerid ASC";
            SQLiteCommand cmd = new SQLiteCommand(sql, con);
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
            DataSet ds = new DataSet();

            da.Fill(ds);
            DataTable dt = ds.Tables[0];
            this.grid4.DataSource = dt;
            con.Close();

        }

        private void cbxRein_SelectedIndexChanged(object sender, EventArgs e)
        {
            string workerinfo = cbxRein.Text;
            string q1 = "SELECT name from users WHERE workerid = '" + workerinfo + "'";
            string q2 = "SELECT surname from users WHERE workerid = '" + workerinfo + "'";
            textBox5.Text = WorkerInfo(q1, textBox5);
            textBox4.Text = WorkerInfo(q2, textBox4);
        }

        private void tabOne_Click(object sender, EventArgs e)
        {

        }

        private void timerpic_Tick(object sender, EventArgs e)
        {
            picwarn.Visible = false;
            lblWarn.Visible = false;
            System.Threading.Thread.Sleep(50);
            picwarn.Visible = true;
            lblWarn.Visible = true;

        }

        private void picwarn_Click(object sender, EventArgs e)
        {

        }

        private void employeeAdministratorToolStripMenuItem_Click(object sender, EventArgs e)
        {

        }

        private void button11_Click(object sender, EventArgs e)
        {
            /* string filename = saveFileDialog1.FileName;
             saveFileDialog1.FileName = FileName;*/
            if (saveFileDialog1.ShowDialog(this) == DialogResult.OK)
                WriteFile(saveFileDialog1.FileName + ".csv");
        }

        private void cbxType_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void pictureBox3_Click(object sender, EventArgs e)
        {
            tabControl1.SelectedTab = tabAdmin;
        }

        private void panel1_Paint(object sender, PaintEventArgs e)
        {

        }

        private void button13_Click(object sender, EventArgs e)
        {
            tabControl1.SelectedTab = tabAdmin;
        }

        private void button18_Click(object sender, EventArgs e)
        {
            tabControl1.SelectedTab = tabDash;
        }

        private void tabDash_Click(object sender, EventArgs e)
        {

        }

        private void button17_Click(object sender, EventArgs e)
        {
            tabControl1.SelectedTab = tabImport;
        }

        private void button20_Click(object sender, EventArgs e)
        {
            string names = edtNAdmin.Text; ;
            string surname = edtSAdmin.Text;
            string pc = edtPC.Text;
            string estatus = edtEStatus.Text;
            string termreason = edtTAdmin.Text;
            string tma = edtMAdmin.Text;
            string tfa = edtFAdmin.Text;
            string tfrl = edtFRAdmin.Text;
            string pl = edtPLAdmin.Text;
            string sl = edtSLAdmin.Text;
            string wid = cbxEAdmin.Text;
            pbxGreen2.Visible = true;
            try
            {
                SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
                con.Open();
                SQLiteCommand cmd = new SQLiteCommand(con);
                cmd.CommandText = "UPDATE users SET name=@name, surname=@surname, status=@pc, working=@working, terminationreason=@termreason, totalfriday=@totalfriday, totalmonday=@totalmonday,totalfamrespon=@totalfamrespon,totalpaidleave=@totalpaidleave,totalsickleave=@totalsickleave WHERE workerid = @workerid";
                cmd.Parameters.Add(new SQLiteParameter("@name", names));
                cmd.Parameters.Add(new SQLiteParameter("@surname", surname));
                cmd.Parameters.Add(new SQLiteParameter("@pc", pc));
                cmd.Parameters.Add(new SQLiteParameter("@working", estatus));
                cmd.Parameters.Add(new SQLiteParameter("@termreason", termreason));
                cmd.Parameters.Add(new SQLiteParameter("@totalfriday", tfa));
                cmd.Parameters.Add(new SQLiteParameter("@totalmonday", tma));
                cmd.Parameters.Add(new SQLiteParameter("@totalfamrespon", tfrl));
                cmd.Parameters.Add(new SQLiteParameter("@totalpaidleave", pl));
                cmd.Parameters.Add(new SQLiteParameter("@totalsickleave", sl));
                cmd.Parameters.Add(new SQLiteParameter("@workerid", wid));
                cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception ex)
            {
                pbxRed2.Visible = true;
            }
        }

        private void button22_Click(object sender, EventArgs e)
        {
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(con);
            cmd.CommandText = "SELECT * From users ORDER By workerid ASC";
            cmd.ExecuteNonQuery();
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            DataTable dt = ds.Tables[0];
            this.gridAdmin.DataSource = dt;
            con.Close();
        }

        private void gridAdmin_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            string wid = gridAdmin.CurrentCell.Value.ToString();
            MessageBox.Show(wid);
        }

        private void button12_Click(object sender, EventArgs e)
        {
            tabControl1.SelectedTab = tabRep;
        }

        private void cbxEAdmin_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbxEAdmin.Text != "")
            {
                string workerinfo = cbxEAdmin.Text;
                string q1 = "SELECT name from users WHERE workerid = '" + workerinfo + "'";
                string q2 = "SELECT surname from users WHERE workerid = '" + workerinfo + "'";
                string q3 = "SELECT status from users WHERE workerid = '" + workerinfo + "'";
                string q4 = "SELECT working from users WHERE workerid = '" + workerinfo + "'";
                string q5 = "SELECT terminationreason from users WHERE workerid = '" + workerinfo + "'";
                string q6 = "SELECT totalfriday from users WHERE workerid = '" + workerinfo + "'";
                string q7 = "SELECT totalmonday from users WHERE workerid = '" + workerinfo + "'";
                string q8 = "SELECT totalfamrespon from users WHERE workerid = '" + workerinfo + "'";
                string q9 = "SELECT totalpaidleave from users WHERE workerid = '" + workerinfo + "'";
                string q10 = "SELECT totalsickleave from users WHERE workerid = '" + workerinfo + "'";
                edtNAdmin.Text = WorkerInfo2(q1, edtNAdmin);
                edtSAdmin.Text = WorkerInfo2(q2, edtSAdmin);
                edtTAdmin.Text = WorkerInfo2(q5, edtTAdmin);
                edtMAdmin.Text = WorkerInfo2(q7, edtMAdmin);
                edtFAdmin.Text = WorkerInfo2(q6, edtFAdmin);
                edtFRAdmin.Text = WorkerInfo2(q8, edtFRAdmin);
                edtSLAdmin.Text = WorkerInfo2(q10, edtSLAdmin);
                edtPLAdmin.Text = WorkerInfo2(q9, edtPLAdmin);
                edtPC.Text = WorkerInfo2(q3, edtPC);
                edtEStatus.Text = WorkerInfo2(q4, edtEStatus);
            }
        }

        private void button21_Click(object sender, EventArgs e)
        {
            pbxGreen2.Visible = false;
            cbxEAdmin.Items.Clear();
            edtNAdmin.Text = "";
            edtSAdmin.Text = "";
            edtTAdmin.Text = "";
            edtMAdmin.Text = "";
            edtFAdmin.Text = "";
            edtFRAdmin.Text = "";
            edtSLAdmin.Text = "";
            edtPLAdmin.Text = "";
            edtPC.Text = "";
            edtEStatus.Text = "";
            string querz = "SELECT workerid FROM users ORDER By workerid ASC";
            FillDropDownList(querz, cbxEAdmin);
        }
        private void setMaxmin()
        {
            dtmFrom.MinDate = DateTime.ParseExact(firstupdate, "dd/MM/yyyy", null);
            dtmFrom.MaxDate = DateTime.ParseExact(lastupdate, "dd/MM/yyyy", null);
            dtmTo.MinDate = DateTime.ParseExact(firstupdate, "dd/MM/yyyy", null);
            dtmTo.MaxDate = DateTime.ParseExact(lastupdate, "dd/MM/yyyy", null);
        }

        public IEnumerable<DateTime> EachDay(DateTime from, DateTime thru)
        {
            for (var day = from.Date; day.Date <= thru.Date; day = day.AddDays(1))
                yield return day;
        }

        private void button23_Click(object sender, EventArgs e)
        {
            int hmany = cbxx.CheckedItems.Count;
            int[] cbx = new int[7];
            int count = 0;

            foreach (int indexChecked in cbxx.CheckedIndices)
            {
                count++;
                cbx[count - 1] = indexChecked;
            }
            grR.Columns.Clear();
            /*0*/
            bool bAttended = false;
            /*1*/
            bool bUL = false;
            /*2*/
            bool bPL = false;
            /*3*/
            bool bPFR = false;
            /*4*/
            bool bUFR = false;
            /*5*/
            bool bUSC = false;
            /*6*/
            bool bAW = false;

            for (int x = 0; x <= hmany - 1; x++)
            {
                int z = cbx[x];
                switch (z)
                {
                    case 0: bAttended = true;
                        break;
                    case 1: bUL = true;
                        break;
                    case 2: bPL = true;
                        break;
                    case 3: bPFR = true;
                        break;
                    case 4: bUFR = true;
                        break;
                    case 5: bUSC = true;
                        break;
                    case 6: bAW = true;
                        break;
                    default: break;
                }
            }

            DateTime fromd = dtmFrom.Value;
            DateTime tod = dtmTo.Value;
            string query = "";
            string worker = cbxReport.Text;
            string dayz = "";
            string item = "";
            int attended = 0;
            int psc = 0;
            int ul = 0;
            int pl = 0;
            int pfr = 0;
            int ufr = 0;
            int usc = 0;
            int aw = 0;
            try
            {
                SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
                foreach (DateTime day in EachDay(fromd, tod))
                {//start foreach
                    dayz = day.ToString("dd/MM/yyyy");
                    query = "SELECT yearly.'" + dayz + "' FROM yearly WHERE workerid = '" + worker + "'";
                    
                    item = GetItem(query);
                    if (bAttended == true)
                    {
                        switch (item)
                        {
                            case "ATTENDED": attended += 1; break;
                            default: break;
                        }
                    }
                    if (bUL == true)
                    {
                        switch (item)
                        {
                            case "UL": ul += 1; break;
                            default: break;
                        }
                    }
                    if (bPL == true)
                    {
                        switch (item)
                        {
                            case "PL": pl += 1; break;
                            default: break;
                        }
                    }
                    if (bPFR == true)
                    {
                        switch (item)
                        {
                            case "PFR": pfr += 1; break;
                            default: break;
                        }
                    }
                    if (bUFR == true)
                    {
                        switch (item)
                        {
                            case "UFR": ufr += 1; break;
                            default: break;
                        }
                    }
                    if (bUSC == true)
                    {
                        switch (item)
                        {
                            case "USC": usc += 1; break;
                            default: break;
                        }
                    }
                    if (bAW == true)
                    {
                        switch (item)
                        {
                            case "AW": aw += 1; break;
                            default: break;
                        }
                    }

                  
                }//end foreach loop

            }//end try
            catch (Exception ex)
            {
                MessageBox.Show("No data exists for those dates");
            }//end catch
            int colcount = 0;
            
            grR.Columns.Insert(colcount, new DataGridViewColumn() { HeaderText = "Employee Number", CellTemplate = new DataGridViewTextBoxCell() });
            grR.Rows[rowcount].Cells[colcount].Value = cbxReport.Text;
            colcount++;
           // grR.Columns.Add(new DataGridViewColumn() { HeaderText = "EmployeeNumber", CellTemplate = new DataGridViewTextBoxCell() });
           // int indexlol = grR.Columns["EmployeeNumber"].DisplayIndex;
           // grR.Rows[0].Cells[grR.Columns.IndexOf(grR.Columns["EmployeeNumber"])].Value = cbxReport.Text;
            if (bAttended == true)
            {
                grR.Columns.Insert(colcount,new DataGridViewColumn() { HeaderText = "Total Attended", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Rows[rowcount].Cells[colcount].Value = attended.ToString();
                colcount++;
            }
            if (bUL == true)
            {
                grR.Columns.Insert(colcount, new DataGridViewColumn() { HeaderText = "Total Unpaid Leave", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Rows[rowcount].Cells[colcount].Value = ul.ToString();
                colcount++;
            }
            if (bPL == true)
            {
                grR.Columns.Insert(colcount, new DataGridViewColumn() { HeaderText = "Total Paid Leave", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Rows[rowcount].Cells[colcount].Value = pl.ToString();
                colcount++;
            }
            if (bPFR == true)
            {
                grR.Columns.Insert(colcount, new DataGridViewColumn() { HeaderText = "Total PFR", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Rows[rowcount].Cells[colcount].Value = pfr.ToString();
                colcount++;
            }
            if (bUFR == true)
            {
                grR.Columns.Insert(colcount, new DataGridViewColumn() { HeaderText = "Total UFR", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Rows[rowcount].Cells[colcount].Value = ufr.ToString();
                colcount++;
            }
            if (bUSC == true)
            {
                grR.Columns.Insert(colcount, new DataGridViewColumn() { HeaderText = "Total USC", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Rows[rowcount].Cells[colcount].Value = usc.ToString();
                colcount++;
            }
            if (bAW == true)
            {
                grR.Columns.Insert(colcount, new DataGridViewColumn() { HeaderText = "Total Awol", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Rows[rowcount].Cells[colcount].Value = aw.ToString();
                colcount++;
                
            }
            rowcount++;
        }//end button click event handler

        private void textBox3_TextChanged(object sender, EventArgs e)
        {

        }

        private void textBox7_TextChanged(object sender, EventArgs e)
        {

        }

        private void textBox8_TextChanged(object sender, EventArgs e)
        {

        }

        private void textBox6_TextChanged(object sender, EventArgs e)
        {

        }

        private void tabPage1_Click(object sender, EventArgs e)
        {

        }

        private void button15_Click(object sender, EventArgs e)
        {
            tabControl1.SelectedTab = tabTerm;
        }
        /* RETURN COUNT VALUES FOR ALL WORKERS */
        private void returnAtt(ref int att, SQLiteConnection con, string column)
        {
            int a = att;
            //    string query = "SELECT Count(*) FROM yearly WHERE yearly.02//12//13 = ATTENDED";        
            string query = "SELECT 04/12/2013 FROM yearly WHERE 04/12/2013 = PSC";
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(con);
            cmd.CommandText = query;
            //   cmd.Parameters.Add(new SQLiteParameter("@p1", column)); 1
            a += (int)cmd.ExecuteScalar();
            att = a;
        }

        private void returnPSC(ref int psc, SQLiteConnection con, string column)
        {
            int a = psc;
            string query = "SELECT Count(yearly.'" + column + "') FROM yearly WHERE yearly.'" + column + "' = PSC";
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(con);
            cmd.CommandText = query;
            a += (int)cmd.ExecuteScalar();
            psc = a;
        }

        private void returnUL(ref int ul, SQLiteConnection con, string column)
        {
            int a = ul;
            string query = "SELECT Count(yearly.'" + column + "') FROM yearly WHERE yearly.'" + column + "' = UL";
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(con);
            cmd.CommandText = query;
            a += (int)cmd.ExecuteScalar();
            ul = a;
        }

        private void returnPL(ref int pl, SQLiteConnection con, string column)
        {
            int a = pl;
            string query = "SELECT Count(yearly.'" + column + "') FROM yearly WHERE yearly.'" + column + "' = PL";
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(con);
            cmd.CommandText = query;
            a += (int)cmd.ExecuteScalar();
            pl = a;
        }

        private void returnPFR(ref int pfr, SQLiteConnection con, string column)
        {
            int a = pfr;
            string query = "SELECT Count(yearly.'" + column + "') FROM yearly WHERE yearly.'" + column + "' = PFR";
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(con);
            cmd.CommandText = query;
            a += (int)cmd.ExecuteScalar();
            pfr = a;
        }

        private void returnUFR(ref int ufr, SQLiteConnection con, string column)
        {
            int a = ufr;
            string query = "SELECT Count(yearly.'" + column + "') FROM yearly WHERE yearly.'" + column + "' = UFR";
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(con);
            cmd.CommandText = query;
            a += (int)cmd.ExecuteScalar();
            ufr = a;
        }

        private void returnUSC(ref int usc, SQLiteConnection con, string column)
        {
            int a = usc;
            string query = "SELECT Count(yearly.'" + column + "') FROM yearly WHERE yearly.'" + column + "' = USC";
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(con);
            cmd.CommandText = query;
            a += (int)cmd.ExecuteScalar();
            usc = a;
        }

        private void returnAW(ref int aw, SQLiteConnection con, string column)
        {
            int a = aw;

            string query = "SELECT Count(yearly.'" + column + "') FROM yearly WHERE yearly.'" + column + "' = AW";
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(con);
            cmd.CommandText = query;
            a += (int)cmd.ExecuteScalar();
            aw = a;
        }

        /*END OF RETURNING ALL VALUES FOR ALL DATA*/
        private void button24_Click(object sender, EventArgs e)
        {
            DateTime fromd = dpFrom.Value;
            DateTime tod = dpTo.Value;
            string query = "";
            string worker = cbxReport.Text;
            string dayz = "";
            string item = "";
            int attended = 0;
            int psc = 0;
            int ul = 0;
            int pl = 0;
            int pfr = 0;
            int ufr = 0;
            int usc = 0;
            int aw = 0;
            try
            {
                for (int z = 0; z <= 7; z++)
                {
                    ch.Series.RemoveAt(z);
                }
            }
            catch { }
            ch.Series.Add("Attended");
            ch.Series.Add("Paid Sick Leave");
            ch.Series.Add("Upaid Sick Leave");
            ch.Series.Add("Unpaid Leave");
            ch.Series.Add("Paid Leave");
            ch.Series.Add("Paid Family Responsibility");
            ch.Series.Add("Unpaid Family Responsiblity");
            ch.Series.Add("Awol");
            //   try
            //     {

            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            foreach (DateTime day in EachDay(fromd, tod))
            {
                dayz = day.ToString("dd/MM/yyyy");
                query = "SELECT yearly.04//12//2013 FROM yearly WHERE 04//12//2013 = PSC";
                returnAtt(ref attended, con, dayz);
                returnAW(ref aw, con, dayz);
                returnPFR(ref pfr, con, dayz);
                returnPL(ref pl, con, dayz);
                returnPSC(ref psc, con, dayz);
                returnUFR(ref ufr, con, dayz);
                returnUL(ref ul, con, dayz);
                returnUSC(ref usc, con, dayz);
                /*item = GetItem(query);
                switch (item)
                {
                    case "ATTENDED": attended += 1; break;
                    case "PSC": psc += 1; break;
                    case "UL": ul += 1; break;
                    case "PL": pl += 1; break;
                    case "PFR": pfr += 1; break;
                    case "UFR": ufr += 1; break;
                    case "USC": usc += 1; break;
                    case "AW": aw += 1; break;
                    default: break;
                }*/
            }
            int total = attended + psc + ul + pl + pfr + ufr + usc + aw;
            ch.Series[0].Points.AddY(attended * 1.0);
            ch.Series[1].Points.AddY(psc);
            ch.Series[2].Points.AddY(ufr);
            ch.Series[3].Points.AddY(ul);
            ch.Series[4].Points.AddY(pl);
            ch.Series[5].Points.AddY(pfr);
            ch.Series[6].Points.AddY(aw);
            ch.Series[7].Points.AddY(aw);
            //  }
            //   catch (Exception ex)
            //   {
            //        MessageBox.Show("No data exists for those dates");
            //}


        }

        private void button14_Click(object sender, EventArgs e)
        {
            tabControl1.SelectedTab = tabGraphs;
        }

        private void button16_Click(object sender, EventArgs e)
        {
            tabControl1.SelectedTab = tabUpdate;
        }

        private void button25_Click(object sender, EventArgs e)
        {
            MessageBox.Show(" ");
        }

        private void dataGridView1_CellContentDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            string wid = grR.CurrentCell.Value.ToString();
            MessageBox.Show(wid);
        }

        private void button26_Click(object sender, EventArgs e)
        {
           
        }

        private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
        {

        }
    }
}
